<?php 
/**
 * 客户应付款统计
 * @copyright   Copyright (c) 2006 - 2010 DZH 友拓软件友拓通
 * @category   	账目信息
 * @package  	Model
 * @author    	李恺弘
 * @version 	2.1,2012-07-22
 */
class ClientStatPublicModel extends AbsStatPublicModel { 
	 
	public $comp_type	=	1;
	
	///字段列表
	private $sqlFields	=	'';
	
	/**
	 * 取合计数据
	 *厂家 229  物流 329 
	 * @return array
	 */ 
	public function getIndex() { 
		if (!empty($_POST['date']['from_paid_date']) && !empty($_POST['date']['to_paid_date'])){ 
			$money = '(should_paid)*income_type*-1'; 
			$having = $_POST['show']==1 ? '' : ' having(sum('.$money.')!=0)';	
			$s_where = 'object_type!=129';	
		}else{
			if ($_POST['date']['lt_paid_date']) {
				$money = '(original_money+discount_money)*income_type*-1';
				$s_where = 'object_type!=129';
			}else {
				$money = 'need_paid*income_type*-1';
				///显示所有 
				$s_where = $_POST['show']==1 ? '( is_close=0 or (  is_close>0 and object_type= 103 and object_type_extend=3 ) )' : 'is_close=0 and need_paid!=0'; 
			}
			/// 是否显示0款项客户
			$having = $_POST['show']==1 ? '' : 'having(sum('.$money.')!=0)';		
		}
 
		$where = _search($s_where.' and '._search_array(_getSpecialUrl($_GET)));  
		
		/// 取明细数组
		$sql_field 		= 'select a.*,sum('.$money.') as total_need_paid '.$this->fundsField().', b.email,b.mobile,b.contact,GROUP_CONCAT(paid_id) as group_paid_id
						from client_paid_detail as a inner join company as b on a.comp_id=b.id and b.comp_type=1 
						where '.$where.' ';
		$sql_group 		 =	' 	group by comp_id,currency_id,basic_id '.$having.'
								order by comp_id,currency_id,basic_id ';
		$sql_group_total =	' group by currency_id '.$having.'
							  order by currency_id ';
		$sql			=	$sql_field.$sql_group; 
		$detail_data	= _listStat($sql);  
		foreach ((array)$detail_data as $key => $value) { 
			$paid_id_array[]	=	intval($value['group_paid_id']); //添加intval 没有id时 下面的sql执行会报错 --zmx 2013-04-09
		} 
		if (is_array($paid_id_array)){
			/// 取指定客户合计数组
			$sql = 'select currency_id,comp_id,sum('.$money.') as total_comp_need_paid
					from client_paid_detail 
					where paid_id in ('.join(',',$paid_id_array).')
					group by comp_id,currency_id  '.$having.'
					order by comp_id,currency_id';
			$main_data = $this->db->query($sql);
		}
		
		foreach ((array)$main_data as $key => $value) {
			$main_data_format[$value['comp_id'].'_'.$value['currency_id']]['total_comp_need_paid']	=	$value['total_comp_need_paid'];
		} 
		$list = array();
		
		if (!empty($_POST['date']['from_paid_date']) && !empty($_POST['date']['to_paid_date'])){
			$url_date = '/from_paid_date/'.formatDate($_POST['date']['from_paid_date']).'/to_paid_date/'.formatDate($_POST['date']['to_paid_date']);
			$sourcetype = 4;
		}else{
			if(empty($_POST['date']['lt_paid_date'])){
				$url_date = '';
				$sourcetype = 1;///3
			}else {
				$url_date = '/to_paid_date/'.formatDate($_POST['date']['lt_paid_date']);
				$sourcetype = 4;
			}			
		}  
		foreach ($detail_data as $key => $value) {
			$value['detail_url'] 		= U('/ClientStat/view/type/'.$sourcetype.'/client_id/'.$value['comp_id'].'/currency_id/'.$value['currency_id'].'/basic_id/'.$value['basic_id'].$url_date);
			$value['total_comp_need_paid']	= $main_data_format[$value['comp_id'].'_'.$value['currency_id']]['total_comp_need_paid'];
			$list[] = $value;
		}  
		$list 	= $this->addRemindMoney(_formatList($list));   
		$list	= $this->getTotalCurrencyInfo(&$list,array('total_need_paid'));    
		///没有分页的合计
		///合计为分页的总数量
		$sql_total		=	$sql_field.$sql_group_total;
		$detail_total 	= _formatList($this->db->query($sql_total));
		$detail_total	= $this->getTotalCurrencyInfo(&$detail_total,array('total_need_paid'));     
		$list['page_total']	= $detail_total;
		
		return $list;
	}
	
	/**
	 * 增加提醒日期
	 *
	 * @param array $list
	 * @param array $info
	 * @return array
	 */
	function addRemindDay(&$list,$info){
		$company_expand	=	S('company_expand');  
		$id_key			=	$info['id']?$info['id']:'comp_id';
		$money_key		=	$info['date']?$info['date']:'edml_total_comp_need_paid';
		if (is_array($list['list'])){
			foreach ($list['list'] as $key => $value) { 
				if ($value[$money_key]>$company_expand[$value[$id_key]]['remind_money'] && $company_expand[$value[$id_key]]['remind_money']>0){
					$list['list'][$key]['tr_color']	=2;
				}else{
					$list['list'][$key]['tr_color']	=1;
				}   
			}
		} 
		return $list;
	}
	   
	/**
	 * 取明细数据
	 *
	 * @return array
	 */
	public function getDetail() {   
		$info	=	$_REQUEST;
		if ($info['client_id']>0){
			$cache_info	=	SOnly('company_expand',intval($info['client_id'])); 
			$today = date("Y-m-d");
			if ($cache_info['remind_day']>0){
				$this->sqlFields	=	' ,if(datediff(\''.$today.'\',paid_date)>'.$cache_info['remind_day'].' && income_type=-1 and need_paid>0 and is_close=0 ,2,1) as tr_color	';
			}else{
				$this->sqlFields	=	' ,1 as tr_color	';
			} 
		}  
		$comp_id	=	$info['comp_id']?$info['comp_id']:$info['client_id']; 
		$where = array(
			'comp_id='.$comp_id,///客户
			'currency_id='.$info['currency_id'],///币种
			'basic_id='.$info['basic_id'],///公
		);  
		 
		  
		if (!empty($info['from_paid_date']) && !empty($info['to_paid_date'])){
				$where[] = 'paid_date<=\''.$info['to_paid_date'].'\' and '.'paid_date>=\''.$info['from_paid_date'].'\'';
		}else {
			if (!empty($info['to_paid_date'])) {
				$where[] = 'paid_date<=\''.$info['to_paid_date'].'\'';
			}			
		}   
		$source_type = intval($info['type']); 
		switch ($source_type){
			case 1:
				/// 未结清款项
				return $this->afterHavePaidList($where);
///				return $this->closeOutList($where);
				break;
			case 2:
				/// 上次平帐后
				return $this->afterList($where);
				break;
			case 3:
				/// 全部款项
///				return $this->afterHavePaidList($where);
				return $this->closeOutList($where);
				break;
			case 4:
				/// 未结清款项
				return $this->closeOutBetweenList($where);
				break;			
		} 
	} 
	 
	/**
	 * 上次对账后的所有款项信息
	 *
	 * @param string $where
	 * @return array
	 */
	private function afterList($where){ 
		!$_GET['to_paid_date'] && $where[] = 'is_close=0'; 
		$sql = 'select *'.$this->sqlFields.' from client_paid_detail where '.implode(' and ',$where).' order by paid_date asc';
		$sql_total = 'select 
			sum(if(income_type<0,original_money,0)) as original_money,
			sum(discount_money) as discount_money,
			sum(should_paid) as should_paid,
			sum(if(income_type<0,0,original_money)) as have_paid,
			sum(if(income_type<0,need_paid,have_paid)) as need_paid,
			sum(if(income_type>0,need_paid,0)) as use_paid, 
			sum(need_paid*income_type*-1) as money
		from client_paid_detail where '.implode(' and ',$where).' order by paid_date asc';
		$list		=	_listStat($sql);  
///		$list = $this->db->query($sql);
		if(empty($list)) return array();
		$sum_need_paid = 0;
 
		foreach ($list as &$value) {
			if ($value['income_type']==1) {
				$value['have_paid'] 		= $value['original_money'];
///				$value['original_money'] 	= $value['should_paid'];
				$value['original_money'] 	= 0;
///				$sum_need_paid 				-= $value['need_paid'];
				$sum_need_paid 				-= $value['should_paid'];
				$value['sum_need_paid']		= $sum_need_paid;
			}else {
				$value['have_paid'] 		= 0; 
///				$sum_need_paid 				+= $value['need_paid'];
				$sum_need_paid 				+= $value['should_paid'];
				$value['sum_need_paid']		= $sum_need_paid;
			} 
			/// 生成备注
			$value = $this->objectTypeCommentSubsidiary($value);	 
		} 
		$list = _formatList($list);
		$list['total']['title']	 = '“'.$list['list'][0]['comp_name'].'”在“'.$list['list'][0]['dd_basic_id'].'”的“'.$list['list'][0]['dd_currency_id'].'”欠款明细：';
		$list['total']['total_sum_need_paid']	= moneyFormat($sum_need_paid,0,C('MONEY_LENGTH'));   
		$list['all_total']						= _listStatTotal($sql_total);
		return $list;
	}
	
	/**
	 * 所有欠款分组显示
	 *
	 * @param string $where
	 * @return array
	 */
	private function closeOutBetweenList($where){
		$sql = 'select *'.$this->sqlFields.' 
		from client_paid_detail where '.implode(' and ',$where).' and object_type!=129 order by paid_date asc,is_close desc';///按照日期在按照打包关系排列lee 110916
		
		$sql_total = 'select 
			sum(if(income_type<0,original_money,0)) as original_money,
			sum(discount_money) as discount_money,
			sum(should_paid) as should_paid,
			sum(if(income_type<0,0,original_money)) as have_paid,
			sum(if(income_type<0,need_paid,have_paid)) as need_paid,
			sum(if(income_type>0,need_paid,0)) as use_paid, 
			sum(need_paid*income_type*-1) as money 
			from client_paid_detail where '.implode(' and ',$where).' and object_type!=129 order by paid_date asc,is_close desc';///按照日期在按照打包关系排列lee 110916
		$list		= _listStat($sql);  
///		$list = $this->db->query($sql); 
		if(empty($list)) return array();
		$sum_need_paid = 0;
		$is_close = -1;
		$total = array();
		foreach ($list as &$value) {
///			if ($is_close!=$value['is_close']) {
///				$sum_need_paid = 0;
///				$is_close = $value['is_close'];
///				$value['total_original_money'] = 0;
///			}else {
				$value['total_original_money'] = $value['total_original_money'];
///			}
			
			if ($value['income_type']==1) {
				$value['have_paid'] 		= $value['original_money'];
				$value['original_money'] 	= 0;
///				$sum_need_paid 				-= $value['need_paid'];
				$sum_need_paid 				-= $value['should_paid'];
				$value['sum_need_paid']		= $sum_need_paid; 
			}else {
				$value['have_paid'] 		= 0;
///				$sum_need_paid 				+= $value['need_paid'];
				$sum_need_paid 				+= $value['should_paid'];
				$value['sum_need_paid']		= $sum_need_paid;
			} 
			/// 生成备注
			$value = $this->objectTypeCommentSubsidiary($value);	
			$total[$is_close]['total_original_money'] 	+= $value['original_money'];
			$total[$is_close]['total_have_paid'] 		+= $value['have_paid'];
			$total[$is_close]['total_discount_money'] 	+= $value['discount_money'];
			$total[$is_close]['total_sum_need_paid'] 	 = moneyFormat($sum_need_paid,0,C('MONEY_LENGTH'));;;
			$total[$is_close]['close_date'] 			 = $value['paid_date'];
		}
		foreach ($list as &$value) {
			$value['total_original_money'] 	= $total[$value['is_close']]['total_original_money'];
			$value['total_have_paid'] 		= $total[$value['is_close']]['total_have_paid'];
			$value['total_discount_money'] 	= $total[$value['is_close']]['total_discount_money'];
			$value['total_sum_need_paid'] 	= $total[$value['is_close']]['total_sum_need_paid'];
			$value['close_date'] 			= $total[$value['is_close']]['close_date'];
		}	 
		$list = _formatList($list);  
		
		foreach ($list['list'] as &$value2) { 
			$value2['title'] 	= '“'.$value2['comp_name'].'('.$value2['dd_currency_id'].')”于“'.$value2['fmd_close_date'].'”前的欠款明细：';
			$last_sum_need_paid	= $value2['dml_sum_need_paid'];	
		}  
		$list['total']['total_sum_need_paid']	=	$last_sum_need_paid;
		$list['all_total']						= _listStatTotal($sql_total);
		return $list;
	}
	 
	/**
	 * 所有欠款分组显示
	 *
	 * @param string $where
	 * @return array
	 */
	private function closeOutList($where){  
		$sql = 'select *'.$this->sqlFields.',if(is_close=0, 99999, is_close) as order_is_close 
				from client_paid_detail where '.implode(' and ',$where).' order by order_is_close,paid_date asc,is_close desc';///按照日期在按照打包关系排列lee 110916
		$sql_total = 'select 
			sum(if(income_type<0,original_money,0)) as original_money,
			sum(discount_money) as discount_money,
			sum(should_paid) as should_paid,
			sum(if(income_type<0,0,original_money)) as have_paid,
			sum(if(income_type<0,need_paid,have_paid)) as need_paid,
			sum(if(income_type>0,need_paid,0)) as use_paid, 
			sum(need_paid*income_type*-1) as money 
			from client_paid_detail where '.implode(' and ',$where).' ';///按照日期在按照打包关系排列lee 110916
		$list		= _listStat($sql);  
		if(empty($list)) return array();
		$sum_need_paid = 0;
		$is_close = -1;
		$total = array();
		foreach ($list as &$value) {
			if ($is_close!=$value['is_close']) {
				$sum_need_paid = 0;
				$is_close = $value['is_close'];
				$value['total_original_money'] = 0;
			}else {
				$value['total_original_money'] = $value['total_original_money'];
			}
			
			if ($value['income_type']==1) {
				$value['have_paid'] 		= $value['original_money'];
				$value['original_money'] 	= 0;
///				$sum_need_paid 				-= $value['need_paid'];
				$sum_need_paid 				-= $value['should_paid'];
				$value['sum_need_paid']		= $sum_need_paid; 
			}else {
				$value['have_paid'] 		= 0;
///				$sum_need_paid 				+= $value['need_paid'];
				$sum_need_paid 				+= $value['should_paid'];
				$value['sum_need_paid']		= $sum_need_paid;
			}   
			/// 生成备注
			$value = $this->objectTypeCommentSubsidiary($value);	 
			$total[$is_close]['total_original_money'] 	+= $value['original_money'];
			$total[$is_close]['total_have_paid'] 		+= $value['have_paid'];
			$total[$is_close]['total_discount_money'] 	+= $value['discount_money'];
			$total[$is_close]['total_sum_need_paid'] 	 = $sum_need_paid; 
			$total[$is_close]['close_date'] 			 = $value['paid_date']; 
		}
		$is_close_max	=	0;
		foreach ($list as &$value) { 
///			if ($value['is_close']>0){ $is_close_max	=	$value['is_close'];}///如果所有信息未平账即日期记录为 "前"
			$value['total_original_money'] 	= moneyFormat($total[$value['is_close']]['total_original_money'],0,C('MONEY_LENGTH'));
			$value['total_have_paid'] 		= moneyFormat($total[$value['is_close']]['total_have_paid'],0,C('MONEY_LENGTH'));
			$value['total_discount_money'] 	= moneyFormat($total[$value['is_close']]['total_discount_money'],0,C('MONEY_LENGTH'));
			$value['total_sum_need_paid'] 	= moneyFormat($total[$value['is_close']]['total_sum_need_paid'],0,C('MONEY_LENGTH'));
			$value['close_date'] 			= $total[$value['is_close']]['close_date'];
		}	  
		$list = _formatList($list);  
		foreach ($list['list'] as &$value2) {  
///			$word	=	($value2['is_close']>0 || $is_close_max==0)?'前':'前';
			$word	=	'前';
			$value2['title'] 	= '“'.$value2['comp_name'].'('.$value2['currency_no'].')”于“'.$value2['fmd_close_date'].'”'.$word.'的欠款明细：';
			$last_sum_need_paid	= $value2['dml_sum_need_paid'];	 
			$list['group_list'][$value2['is_close']][]	=	$value2;
		}  
		$list['total']['total_sum_need_paid']	=	$last_sum_need_paid;   
		$list['all_total']						= _listStatTotal($sql_total);
		return $list;
	}
	 
	/**
	 * 对账后的所有款项信息(未被使用完的)
	 *
	 * @param string $where
	 * @return array
	 */
	private function afterHavePaidList($where){
		$where[] 	= 'is_close=0 and need_paid<>0 '; 
		$sql 		= 'select *'.$this->sqlFields.' from client_paid_detail where '.implode(' and ',$where).' order by paid_date asc';
		$sql_total 	= 'select 
						sum(if(income_type<0,original_money,0)) as original_money,
						sum(discount_money) as discount_money,
						sum(should_paid) as should_paid,
						sum(if(income_type<0,have_paid,original_money)) as have_paid,
						sum(if(income_type<0,need_paid,have_paid)) as need_paid,
						sum(if(income_type>0,need_paid,0)) as use_paid, 
						sum(need_paid*income_type*-1) as money
						from client_paid_detail where '.implode(' and ',$where).' order by paid_date asc';
		$list		= _listStat($sql);   
		if(empty($list)) return array();
		$sum_need_paid = 0;
		foreach ($list as &$value) {
			if ($value['income_type']>0) {
				$value['use_paid']		= $value['need_paid'];
				$value['have_paid'] 	= $value['original_money'];
				$sum_need_paid 			-= $value['need_paid'];
				$value['sum_need_paid']	= $sum_need_paid;
				$value['need_paid']		= $value['original_money'] = 0; 
			}else {
				$value['use_paid']		= 0;
				$sum_need_paid 			+= $value['need_paid'];
				$value['sum_need_paid']	= $sum_need_paid;
			}
			/// 生成备注
			$value = $this->objectTypeCommentSubsidiary($value);	 
		}
		$list = _formatList($list);
		$list['total']['title']	 				= '“'.$list['list'][0]['comp_name'].'”'.L('in').'“'.$list['list'][0]['dd_basic_id'].'”'.L('de').'“'.$list['list'][0]['dd_currency_id'].'”'.L('funds_detail').'：';
		$list['total']['total_sum_need_paid']	= moneyFormat($sum_need_paid,0,C('MONEY_LENGTH'));
		$list['all_total']						= _listStatTotal($sql_total);
		return $list;
	} 
}